library(tidyverse)
library(cepespR)
library(readxl)
library(data.table)
library(stargazer)
library(haven)

#utilizar file= arquivo1.csv
library(readr)
arquivo1 <- read_delim("arquivo1.csv", 
                       delim = ";", escape_double = FALSE, locale = locale(decimal_mark = ",", 
                                                                           grouping_mark = ".", encoding = "WINDOWS-1252"), 
                       trim_ws = TRUE)


FN_e<-filter(arquivo1,Dep_Federal==0)
FN_f<-filter(arquivo1,Dep_Federal==1)

FN_f%>%group_by(ANO_ELEICAO,UF)%>%count(Dep_Federal,sort=TRUE)->TabelaF

TabelaF <- TabelaF %>% group_by( UF ,ANO_ELEICAO ) %>%
  summarise(Valor = sum(n))%>%
  spread(ANO_ELEICAO,Valor)


Media<-rowMeans(TabelaF[2:4])
Media<-as.data.frame(Media)
Media<-round(Media, digits = 1)

TabelaF<-bind_cols(TabelaF,Media)


FN_e$estadual<-1
FN_e%>%group_by(ANO_ELEICAO,UF)%>%count(estadual,sort=TRUE)->TabelaE

TabelaE <- TabelaE %>% group_by( UF ,ANO_ELEICAO ) %>%
  summarise(Valor = sum(n))%>%
  spread(ANO_ELEICAO,Valor)


Media<-rowMeans(TabelaE[2:4])
Media<-as.data.frame(Media)
Media<-round(Media, digits = 1)

TabelaE<-bind_cols(TabelaE,Media)

UF <- c("SP", "MG", "RJ", "BA", "RS","PR","PE","CE","MA","GO","PA","SC",
      "PB","ES","PI","AL","AC","AM","AP","MS","MT","RN","RO","RR","SE","TO")
Cadeiras_F<- c(70,53,46,39,31,30,25,22,18,17,17,16,12,10,10,9,8,8,8,8,8,
               8,8,8,8,8)
Cadeiras_E<-c(94,77,70,63,55,54,54,46,42,41,41,40,36,30,30,27,24,24,24,24,24,
              24,24,24,24,24)
Munic<-c(645,853,92,417,497,399,185,184,217,246,144,295,223,78,224,102,22,
         62,16,79,141,167,52,15,75,139)

CF <- data.frame(UF, Cadeiras_F)
CE <- data.frame(UF, Cadeiras_E)

Munic<-data.frame(UF,Munic)

TabelaF<-TabelaF%>%mutate(Total=`2010`+`2014`+`2018`)
TabelaE<-TabelaE%>%mutate(Total=`2010`+`2014`+`2018`)

TabelaF<-left_join(TabelaF,CF,by="UF")
TabelaE<-left_join(TabelaE,CE,by="UF")

TabelaF<-left_join(TabelaF,Munic,by="UF")
TabelaE<-left_join(TabelaE,Munic,by="UF")

TabelaF<-TabelaF%>%mutate(Casos=Total*Munic)
TabelaE<-TabelaE%>%mutate(Casos=Total*Munic)

Tabela1.1<-select(TabelaF,UF,Cadeiras_F,"2010":"2018",Media)
Tabela1.2<-select(TabelaE,UF,Cadeiras_E,"2010":"2018",Media)


Tabela1<-left_join(Tabela1.1,Tabela1.2,by="UF")
rm(Tabela1.1,Tabela1.2)

Tabela1<-arrange(Tabela1,desc(Cadeiras_F))

Tabela1<-rename(Tabela1,"2010F"="2010.x")
Tabela1<-rename(Tabela1,"2014F"="2014.x")
Tabela1<-rename(Tabela1,"2018F"="2018.x")
Tabela1<-rename(Tabela1,"MediaF"="Media.x")
Tabela1<-rename(Tabela1,"2010E"="2010.y")
Tabela1<-rename(Tabela1,"2014E"="2014.y")
Tabela1<-rename(Tabela1,"2018E"="2018.y")
Tabela1<-rename(Tabela1,"MediaE"="Media.y")



Tabela2.1<-select(TabelaF,UF,Total,Munic,Casos)
Tabela2.2<-select(TabelaE,UF,Total,Casos)
Tabela2<-left_join(Tabela2.1,Tabela2.2,by="UF")
rm(Tabela2.1,Tabela2.2)

Tabela2<-select(Tabela2,UF,Total.x,Total.y,Munic,Casos.x,Casos.y)
Tabela2<-arrange(Tabela2,desc(Total.x))

Tabela2<-rename(Tabela2,"TotalF"="Total.x")
Tabela2<-rename(Tabela2,"TotalE"="Total.y")
Tabela2<-rename(Tabela2,"CasosF"="Casos.x")
Tabela2<-rename(Tabela2,"CasosE"="Casos.y")

rm(CE,CF,FN_e, FN_f,Media,Munic,TabelaE,TabelaF)


